using System.Data;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;

namespace ServiceStack.OrmLite.Tests;

[TestFixtureOrmLite]
public class ShippersExample(DialectContext context) : OrmLiteProvidersTestBase(context)
{
	[Alias("Shippers")]
	public class Shipper
		: IHasId<int>
	{
		[AutoIncrement]
		[Alias("ShipperID")]
		public int Id { get; set; }

		[Required]
		[Index(Unique = true)]
		[StringLength(40)]
		public string CompanyName { get; set; }

		[StringLength(24)]
		public string Phone { get; set; }

		[References(typeof(ShipperType))]
		public int ShipperTypeId { get; set; }
	}

	[Alias("ShipperTypes")]
	public class ShipperType
		: IHasId<int>
	{
		[AutoIncrement]
		[Alias("ShipperTypeID")]
		public int Id { get; set; }

		[Required]
		[Index(Unique = true)]
		[StringLength(40)]
		public string Name { get; set; }
	}

	public class SubsetOfShipper
	{
		[Alias("ShipperID")]
		public int ShipperID { get; set; }
		public string CompanyName { get; set; }
	}

	public class ShipperTypeCount
	{
		[Alias("ShipperTypeID")]
		public int ShipperTypeId { get; set; }
		public int Total { get; set; }
	}


	[Test]
	public void Shippers_UseCase()
	{
		using IDbConnection db = OpenDbConnection();
		db.DropTable<Shipper>();
		db.DropTable<ShipperType>();

		db.CreateTable<ShipperType>();
		db.CreateTable<Shipper>();

		var trainsType = new ShipperType { Name = "Trains" };
		var planesType = new ShipperType { Name = "Planes" };

		//Playing with transactions
		using (IDbTransaction dbTrans = db.OpenTransaction())
		{
			db.Save(trainsType);
			db.Save(planesType);

			dbTrans.Commit();
		}

		using (IDbTransaction dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
		{
			db.Insert(new ShipperType { Name = "Automobiles" });
			Assert.That(db.Select<ShipperType>(), Has.Count.EqualTo(3));
		}
		Assert.That(db.Select<ShipperType>(), Has.Count.EqualTo(2));


		// Performing standard Insert's and Selects
		db.Insert(new Shipper { CompanyName = "Trains R Us", Phone = "555-TRAINS", ShipperTypeId = trainsType.Id });
		db.Insert(new Shipper { CompanyName = "Planes R Us", Phone = "555-PLANES", ShipperTypeId = planesType.Id });
		db.Insert(new Shipper { CompanyName = "We do everything!", Phone = "555-UNICORNS", ShipperTypeId = planesType.Id });

		var trainsAreUs = db.Single<Shipper>(q => q.ShipperTypeId == trainsType.Id);
		Assert.That(trainsAreUs.CompanyName, Is.EqualTo("Trains R Us"));

		trainsAreUs = db.Single<Shipper>("ShipperTypeId".SqlColumn(DialectProvider) + " = @Id".PreNormalizeSql(db), new { trainsType.Id });
		Assert.That(trainsAreUs.CompanyName, Is.EqualTo("Trains R Us"));


		Assert.That(db.Select<Shipper>(q => q.CompanyName == "Trains R Us" || q.Phone == "555-UNICORNS"), Has.Count.EqualTo(2));
		Assert.That(db.Select<Shipper>("CompanyName".SqlColumn(DialectProvider) + " = @company OR Phone = @phone".PreNormalizeSql(db), 
			new { company = "Trains R Us", phone = "555-UNICORNS" }), Has.Count.EqualTo(2));

		Assert.That(db.Select<Shipper>(q => q.ShipperTypeId == planesType.Id), Has.Count.EqualTo(2));
		Assert.That(db.Select<Shipper>("ShipperTypeId".SqlColumn(DialectProvider) + " = @Id".PreNormalizeSql(db), new { planesType.Id }), Has.Count.EqualTo(2));

		//Lets update a record
		trainsAreUs.Phone = "666-TRAINS";
		db.Update(trainsAreUs);
		Assert.That(db.SingleById<Shipper>(trainsAreUs.Id).Phone, Is.EqualTo("666-TRAINS"));
				
		//Then make it disappear
		db.Delete(trainsAreUs);
		Assert.That(db.SingleById<Shipper>(trainsAreUs.Id), Is.Null);

		//And bring it back again
		db.Insert(trainsAreUs);


		//Performing custom queries
		//Select only a subset from the table
		var partialColumns = db.Select<SubsetOfShipper>(db.From<Shipper>().Where(q => q.ShipperTypeId == planesType.Id));
		Assert.That(partialColumns, Has.Count.EqualTo(2));

		var sql = "ShipperTypeId".SqlColumn(DialectProvider) + " = @Id".PreNormalizeSql(db);
		partialColumns = db.Select<SubsetOfShipper>(typeof(Shipper), sql, new { planesType.Id });
		Assert.That(partialColumns, Has.Count.EqualTo(2));

		//Select into another POCO class that matches sql
		var rows = db.SqlList<ShipperTypeCount>(
			"SELECT {0}, COUNT(*) AS Total FROM {1} GROUP BY {0} ORDER BY Total".Fmt(
				DialectProvider.Column<Shipper>(x => x.ShipperTypeId),
				DialectProvider.Table<Shipper>()));

		Assert.That(rows, Has.Count.EqualTo(2));
		Assert.That(rows[0].ShipperTypeId, Is.EqualTo(trainsType.Id));
		Assert.That(rows[0].Total, Is.EqualTo(1));
		Assert.That(rows[1].ShipperTypeId, Is.EqualTo(planesType.Id));
		Assert.That(rows[1].Total, Is.EqualTo(2));

		TestCustomGroupBy(db, trainsType, planesType);


		//And finally lets quickly clean up the mess we've made:
		db.DeleteAll<Shipper>();
		db.DeleteAll<ShipperType>();

		Assert.That(db.Select<Shipper>(), Has.Count.EqualTo(0));
		Assert.That(db.Select<ShipperType>(), Has.Count.EqualTo(0));
	}

	private static void TestCustomGroupBy(IDbConnection db, ShipperType trainsType, ShipperType planesType)
	{
		var rows = db.SqlList<ShipperTypeCount>(
			db.From<Shipper>()
				.GroupBy(x => x.ShipperTypeId)
				.OrderBy("Total")
				.Select(x => new {x.ShipperTypeId, Total = Sql.As(Sql.Count("*"), "Total")}));

		Assert.That(rows, Has.Count.EqualTo(2));
		Assert.That(rows[0].ShipperTypeId, Is.EqualTo(trainsType.Id));
		Assert.That(rows[0].Total, Is.EqualTo(1));
		Assert.That(rows[1].ShipperTypeId, Is.EqualTo(planesType.Id));
		Assert.That(rows[1].Total, Is.EqualTo(2));

		var qGroup = db.From<Shipper>()
			.GroupBy(x => x.ShipperTypeId)
			.OrderBy("2")
			.Select(x => new {x.ShipperTypeId, Total = Sql.Count("*")});

		var rowsGroup = db.Dictionary<int, int>(qGroup);

		Assert.That(rowsGroup, Has.Count.EqualTo(2));
		Assert.That(rowsGroup[trainsType.Id], Is.EqualTo(1));
		Assert.That(rowsGroup[planesType.Id], Is.EqualTo(2));
	}
}